﻿Imports System
Imports System.Data.SqlClient

Public Class hDBase

    Private myConnect As SqlConnection
    Private mySqlDa As SqlDataAdapter
    Private mySqlCmd As SqlCommand
    Private myDataset As New DataSet

    Public Sub Connection(ByVal sStrConn As String)
        myConnect = New SqlConnection
        myConnect.ConnectionString = sStrConn
        myConnect.Open()
    End Sub

    Public Function MyConnection(ByVal connectionString As String) As SqlConnection
        MyConnection = New SqlConnection
        MyConnection.ConnectionString = connectionString
        MyConnection.Open()
        Return MyConnection
    End Function

    Public Sub Disconnect()
        myConnect.Close()
    End Sub

    ''' <summary>
    ''' Hàm dùng để lấy dữ liệu từ một bảng
    ''' </summary>
    ''' <param name="StoreName">Tên thủ tục</param>
    ''' <param name="TableName">Tên bảng CSDL</param>
    ''' <returns>Dữ liệu trả về dataset</returns>
    ''' <remarks></remarks>
    Public Function GetData(ByVal StoreName As String, ByVal TableName As String) As DataSet
        mySqlDa = New SqlDataAdapter
        mySqlCmd = New SqlCommand(StoreName, myConnect)
        mySqlCmd.CommandType = CommandType.StoredProcedure
        mySqlDa.SelectCommand = mySqlCmd
        mySqlDa.Fill(myDataset, TableName)
        Return myDataset
    End Function

    Public Function GetData_BySqlDA(ByVal StoreName As String) As SqlDataAdapter
        Dim sqla As New SqlDataAdapter
        If myConnect.State = 0 Then
            myConnect.Open()
        End If
        sqla.SelectCommand = New SqlCommand
        sqla.SelectCommand.Connection = myConnect
        sqla.SelectCommand.CommandType = CommandType.StoredProcedure
        sqla.SelectCommand.CommandText = StoreName
        Return sqla
    End Function
    ''' <summary>
    ''' Thủ tục thực hiện lệnh sử dụng sqlcommand
    ''' </summary>
    ''' <param name="StoreProcName">Tên thủ tục cần xử lý</param>
    ''' <param name="ArrayParamater">Mảng tham số cần đưa vào</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function RunSqlCommand(ByVal StoreProcName As String, ByVal ArrayParamater() As IDataParameter) As SqlCommand
        If myConnect.State = 0 Then
            myConnect.Open()
        End If
        mySqlCmd = New SqlCommand(StoreProcName, myConnect)
        mySqlCmd.CommandType = CommandType.StoredProcedure
        Dim sqlp As SqlParameter
        For Each sqlp In ArrayParamater
            mySqlCmd.Parameters.Add(sqlp)
        Next
        Return mySqlCmd
    End Function
    ''' <summary>
    ''' Hàm thực thi các lệnh update, insert, delete
    ''' </summary>
    ''' <param name="StoreProcName">Tên thủ tục</param>
    ''' <param name="TableName">Tên bảng CSDL</param>
    ''' <param name="ArrayParamater">Mảng tham số</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function RunStoreProcedures(ByVal StoreProcName As String, ByVal TableName As String, ByVal ArrayParamater() As IDataParameter) As DataSet
        Dim sqld As New SqlCommand
        sqld  = RunSqlCommand(StoreProcName, ArrayParamater)
        mySqlDa = New SqlDataAdapter
        mySqlDa.SelectCommand = sqld
        mySqlDa.MissingSchemaAction = MissingSchemaAction.AddWithKey
        mySqlDa.Fill(myDataset, TableName)
        mySqlDa.Dispose()
        Return myDataset
    End Function

End Class
